package analyze;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class Haha {
	static Connection conn=null;	//	连接对象
	static PreparedStatement stmt=null;	//	发送sql语句
	static ResultSet rs=null;	//	sql返回的结果集
	static Student student=new Student();	//	student对象
	static ArrayList<String> arr=new ArrayList<String>();//4人寝
	static ArrayList<String> arr2=new ArrayList<String>();//6人寝
	static ArrayList<String> arr3=new ArrayList<String>();//6人寝
	static int i=0; //导入excel时位置的标记
	public static void main(String[] args) {
		//		启动程序 设置学生表的test的属性的值（java技术男4）
		selectStudent();
		//分配		
		allot();
		//将数据存在student中
		createExcel(arr);
		createExcel(arr2);
		createExcel(arr3);

	}
	private static void createExcel(ArrayList<String> arr){
		// 第一步，创建一个webbook，对应一个Excel文件  
		HSSFWorkbook wb = new HSSFWorkbook();  
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
		HSSFSheet sheet = wb.createSheet("学生表一");  
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
		HSSFRow row = sheet.createRow((int) 0);  
		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();  
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  

		HSSFCell cell = row.createCell((short) 0);  
		cell.setCellValue("班级");  
		cell.setCellStyle(style);  
		cell = row.createCell((short) 1);  
		cell.setCellValue("姓名");  
		cell.setCellStyle(style);  
		cell = row.createCell((short) 2);  
		cell.setCellValue("房间大小");  
		cell.setCellStyle(style);  
		cell = row.createCell((short) 3);  
		cell.setCellValue("性别");  
		cell.setCellStyle(style);  

		String sql="select className,name,roomSize,sex from student where id=?";//查询student数据
		conn=Conn.getConn();//获得数据库连接

		try {
			stmt=conn.prepareStatement(sql);//	执行sql语句
			for (String id : arr) {
				stmt.setString(1,id);
				rs=stmt.executeQuery();//	
				while(rs.next()){
					row = sheet.createRow(i++);  
					System.out.println(i);
					row.createCell((short) 0).setCellValue( rs.getString(1));  
					row.createCell((short) 1).setCellValue(rs.getString(2));  
					row.createCell((short) 2).setCellValue(rs.getString(3));  
					row.createCell((short) 3).setCellValue(rs.getString(4));
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//			关闭数据库连接
			Conn.close(conn, stmt, rs);
		}
		// 第六步，将文件存到指定位置  
		try  
		{  
			i=0;
			FileOutputStream fout = new FileOutputStream("E:/student"+arr.hashCode()+".xls");  
			wb.write(fout);  
			fout.close();  
		}
		catch (Exception e)  
		{  
			e.printStackTrace();  
		}  
	}
	//	寝室分分配
	private static void allot(){
		for (String className: selectClassName()) {
			ArrayList<String> arrM=findMelancholicName(className);//youyujava技术男4
			ArrayList<String> arrC=findCholericName(className);
			ArrayList<String> arrP=findPhlegmaticName(className);
			ArrayList<String> arrS=findSanguineName(className);
			int lengthM=arrM.size();
			int lengthC=arrC.size();
			int lengthP=arrP.size();
			int lengthS=arrS.size();
			int[] temp={lengthM,lengthC,lengthP,lengthS};
			Arrays.sort(temp);
			//			4人间
			if(className.substring(className.length()-1,className.length()).equals("4")){
				for(int i=0;i<temp[3];i++){
					try{
						arr.add(arrM.get(0));
						arrM.remove(0);
					} catch (IndexOutOfBoundsException e){
					}
					try{
						arr.add(arrC.get(0));
						arrC.remove(0);
					}catch (IndexOutOfBoundsException e){
					}
					try{
						arr.add(arrP.get(0));
						arrP.remove(0);
					} catch (IndexOutOfBoundsException e){
					}
					try{
						arr.add(arrS.get(0));
						arrS.remove(0);
					} catch (IndexOutOfBoundsException e){
					}
				}
			}else if(className.substring(className.length()-1,className.length()).equals("6")){
				for(int i=0;i<temp[3];i++){
					try{
						arr2.add(arrM.get(0));
						arrM.remove(0);
					} catch (IndexOutOfBoundsException e){
					}
					try{
						arr2.add(arrC.get(0));
						arrC.remove(0);
					}catch (IndexOutOfBoundsException e){
					}
					try{
						arr2.add(arrP.get(0));
						arrP.remove(0);
					} catch (IndexOutOfBoundsException e){
					}
					try{
						arr2.add(arrS.get(0));
						arrS.remove(0);
					} catch (IndexOutOfBoundsException e){
					}
					try{
						arr2.add(arrM.get(0));
						arrM.remove(0);
					} catch (IndexOutOfBoundsException e){
					}
					try{
						arr2.add(arrC.get(0));
						arrC.remove(0);
					}catch (IndexOutOfBoundsException e){
					}
				}
			}else {
				for(int i=0;i<temp[3];i++){
					try{
						arr3.add(arrM.get(0));
						arrM.remove(0);
					} catch (IndexOutOfBoundsException e){
					}
					try{
						arr3.add(arrC.get(0));
						arrC.remove(0);
					}catch (IndexOutOfBoundsException e){
					}
					try{
						arr3.add(arrP.get(0));
						arrP.remove(0);
					} catch (IndexOutOfBoundsException e){
					}
					try{
						arr3.add(arrS.get(0));
						arrS.remove(0);
					} catch (IndexOutOfBoundsException e){
					}
				}

			}

		}
	}
	private static ArrayList<String> selectClassName(){
		String sql="select distinct test from student";//查询student数据
		ArrayList<String> a=new ArrayList<String>();
		conn=Conn.getConn();//获得数据库连接
		try {
			stmt=conn.prepareStatement(sql);//	执行sql语句
			rs=stmt.executeQuery();//	班级的种类
			while(rs.next()){
				a.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//			关闭数据库连接
			Conn.close(conn, stmt, rs);
		}
		return a;
	}
	//	查询所有人员的数据 实例出student对象
	private static void selectStudent(){
		String sql="select name,sex,roomSize,temperament,className,id from student";//查询student数据
		conn=Conn.getConn();//获得数据库连接
		try {
			stmt=conn.prepareStatement(sql);//	执行sql语句
			rs=stmt.executeQuery();//	班级的种类
			while(rs.next()){
				student.setName(rs.getString(1));
				student.setSex(rs.getString(2));
				student.setRoomSize(rs.getString(3));
				student.setTemperament(rs.getString(4));
				student.setClassName(rs.getString(5));
				student.setId(rs.getString(6));
				//				将查询的数据存储在数据库中通过id
				setTest(rs.getString(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//			关闭数据库连接
			Conn.close(conn, stmt, rs);
		}
	}
	//	设置student中的test属性值
	private static void setTest(String id){
		String sql="update student set test = '"+getString()+"' where id="+id;
		//		String sql="update student set name = '"+getString()+"' where id="+id;
		System.out.println(sql);
		conn=Conn.getConn();
		try {
			stmt=conn.prepareStatement(sql);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Conn.close(conn, stmt);
		}
	}
	//	test的值得具体拼装
	private static String getString(){
		//		班级性别房间大小
		String s=student.getClassName()+student.getSex()+student.getRoomSize();
		//		String s=null;
		//		i++;
		//		s="小明"+String.valueOf(i);
		return s;
	}
	//	将已经分配的人员标记为1，代表应经分配过寝室
	public static void setSign(String id){
		String sql="update student set sign='1' where id=?";
		conn=Conn.getConn();
		try {
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, id);
			stmt.executeUpdate();
			System.out.println(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Conn.close(conn, stmt);
		}
	}


	//"melancholic",
	public static ArrayList<String> findMelancholicName(String test){
		//		寝室的核心人员数量sql查询语句 核心人员为领导型
		String sql="select id,className,name from student where test=? and sign='0' and temperament like '%elancholi%'";
		conn=Conn.getConn();
		ArrayList<String> arrM=new ArrayList<String>();
		try {
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, test);
			rs=stmt.executeQuery();
			while(rs.next()){
				//为bean对象赋值
				student.setId(rs.getString(1));
				student.setClassName(rs.getString(2));
				student.setName(rs.getString(3));
				//				标记为已经分配
				setSign(rs.getString(1));
				arrM.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Conn.close(conn, stmt, rs);
		}
		//		失败返回空	
		return arrM;
	}
	//	寝室的choleric人员的寻找返回 name
	public static ArrayList<String> findCholericName(String test){
		//		寝室的核心人员数量sql查询语句 核心人员为领导型
		String sql="select id,className,name from student where test=? and sign='0' and temperament like '%holeri%'";
		conn=Conn.getConn();
		ArrayList<String> arrC=new ArrayList<String>();
		try {
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, test);
			rs=stmt.executeQuery();
			while(rs.next()){
				student.setId(rs.getString(1));
				student.setClassName(rs.getString(2));
				student.setName(rs.getString(3));
				//				标记为已经分配
				setSign(rs.getString(1));
				arrC.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Conn.close(conn, stmt, rs);
		}
		//		失败返回空	
		return arrC;
	}
	//	寝室的sanguine人员的寻找返回 name
	public static ArrayList<String> findSanguineName(String test){
		//		寝室的核心人员数量sql查询语句 核心人员为领导型
		String sql="select id,className,name from student where test=? and sign='0' and temperament like '%anguin%'";
		conn=Conn.getConn();
		ArrayList<String> arrS=new ArrayList<String>();
		try {
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, test);
			rs=stmt.executeQuery();
			while(rs.next()){
				student.setId(rs.getString(1));
				student.setClassName(rs.getString(2));
				student.setName(rs.getString(3));
				//				标记为已经分配
				setSign(rs.getString(1));
				arrS.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Conn.close(conn, stmt, rs);
		}
		//		失败返回空	
		return arrS;
	}
	//	寝室的phlegmatic人员的寻找返回 name
	public static ArrayList<String> findPhlegmaticName(String test){
		//		寝室的核心人员数量sql查询语句 核心人员为领导型
		String sql="select id,className,name from student where test=? and sign='0' and temperament like '%hlegmati%'";
		conn=Conn.getConn();
		ArrayList<String> arrP=new ArrayList<String>();
		try {
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, test);
			rs=stmt.executeQuery();
			while(rs.next()){
				student.setId(rs.getString(1));
				student.setClassName(rs.getString(2));
				student.setName(rs.getString(3));
				//				标记为已经分配
				setSign(rs.getString(1));
				arrP.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Conn.close(conn, stmt, rs);
		}
		//		失败返回空	
		return arrP;
	}
}
